<?php
namespace Operationtest\Controller;

use Zend\Mvc\Controller\AbstractActionController;
use Zend\EventManager\EventManagerInterface;
use Zend\View\Model\ViewModel;
use Zend\Session\Container;
use Operationtest\Classe\WhereQueryBySelection;
use Operationtest\Classe\OperationDate;
use Operationtest\Classe\Table;
use Operationtest\Classe\ExcelManager;

class SyntheseController extends AbstractActionController
{
    protected $saleheadTable;
    protected $salerowTable;
    protected $storeTable;
    protected $meteoTable;
    protected $storecountTable;
    protected $marcheTable;
    protected $marcheStoreTable;
    protected $rhdataTable;
    
    public function setEventManager(EventManagerInterface $events) {
        parent::setEventManager($events);
 
        $controller = $this;
        $events->attach('dispatch', function ($e) use ($controller) {
            $controller->layout()->setTemplate('layout/operationtest');
        }, 100);
 
        return $this;
    }
    
    public function indexAction(){
        $table = new Table($sm = $this->getServiceLocator());
        $operationDate = new OperationDate();
        
        $request = $this->getRequest();
        $tableArray = array(
            'marcheStoreTable' => $table->getMarchestoreTable(),
            'marcheTable' => $table->getMarcheTable(),
            'storeTable' => $table->getStoreTable()
        );
        
        $whereQueryBySelection = new WhereQueryBySelection($tableArray);
        
        if($request->isPost()) {
            // CLAUSE WHERE
            $defaultWhere = $whereQueryBySelection->queryForSynthese($request);
            $otherWhere = $whereQueryBySelection->queryForSynthese($request, 'date');
            
            // CLAUSE GROUPBY
            $groupby = " GROUP BY saleHead.orderDate";            
            $ecgroupby = " GROUP BY date";            
            $rptgroupby = " GROUP BY saleHead.orderDate, Article.idArticle";
            $tmgroupby = " GROUP BY saleHead.orderDate"; 
            $tvagroupbay = "GROUP BY saleHead.orderDate, saleRow.taxeRate";
            
            $groupby2 = " GROUP BY saleHead.idStore, saleHead.orderDate";
            $ecgroupby2 = " GROUP BY idStore, date";
            
            // MISE EN SESSION DES VALEURS POUR LES RECUPERER DANS UNE AUTRE ACTION
            $dataSynthese = new Container('dataSynthese');
            
            $dataSynthese->period = $operationDate->displayPeriod($request);
            $dataSynthese->period2 = $operationDate->displayPeriod2($request);
            $dataSynthese->store = $whereQueryBySelection->getTabStores($request);
            
            $dataSynthese->ca = $table->getSaleheadTable()->getSynthCa($defaultWhere, $groupby);   
            $dataSynthese->vtva = $table->getSaleheadTable()->getSynthVentilationTva($defaultWhere, $tvagroupbay);
            $dataSynthese->tm = $table->getSaleheadTable()->getSynthTmTtc($defaultWhere, $tmgroupby);
            $dataSynthese->nbT = $table->getSaleheadTable()->getSynthTransaction($defaultWhere, $groupby);            
            $dataSynthese->nbA = $table->getSalerowTable()->getNbArticle($defaultWhere, $groupby);
            $dataSynthese->rptProduit = $table->getSalerowTable()->getRapportProduit($defaultWhere, $rptgroupby); 
            $dataSynthese->meteoDays = $table->getMeteoTable()->getSMeteos($otherWhere);
            $dataSynthese->ecDays = $table->getStorecountTable()->getSynthEcarts($otherWhere, $ecgroupby);            
            $dataSynthese->repasDays = $table->getSaleheadTable()->getSynthRepas($defaultWhere, $groupby);
            $dataSynthese->ac = $table->getSaleheadTable()->getSynthAnnulationCaisse($defaultWhere, $groupby);
            $dataSynthese->banque = $table->getStorecountTable()->getSynthBanques($otherWhere, $ecgroupby);  
            $dataSynthese->mo = $table->getRhdataTable()->getSynthMO($otherWhere, $ecgroupby);  
            
            $dataSynthese->ca2 = $table->getSaleheadTable()->getSynthCa($defaultWhere, $groupby2);
            $dataSynthese->nbT2 = $table->getSaleheadTable()->getSynthTransaction($defaultWhere, $groupby2);
            $dataSynthese->nbA2 = $table->getSalerowTable()->getNbArticle($defaultWhere, $groupby2);
            $dataSynthese->ecDays2 = $table->getStorecountTable()->getSynthEcarts($otherWhere, $ecgroupby2);
            $dataSynthese->repasDays2 = $table->getSaleheadTable()->getSynthRepas($defaultWhere, $groupby2);
            $dataSynthese->ac2 = $table->getSaleheadTable()->getSynthAnnulationCaisse($defaultWhere, $groupby2);
            $dataSynthese->banque2 = $table->getStorecountTable()->getSynthBanques($otherWhere, $ecgroupby2);
            $dataSynthese->mo2 = $table->getRhdataTable()->getSynthMO($otherWhere, $ecgroupby2);
            
            // BUDGETS ///////////////////////////////////////////////////////////////////////////
            $budgetWhere = $whereQueryBySelection->budgetQueryForTableauDeBord($request, 'month');
            $budgetResult = $table->getBudgetTable()->getBudgetForTableauDeBord($budgetWhere);
            $budgets = $budgetTmp = array();
            foreach($budgetResult as $budget) {
                $budgetTmp[$budget->month][$budget->idStore]['totttc'] = $budget->totttc;
                $budgetTmp[$budget->month][$budget->idStore]['tm'] = $budget->tm;
                $budgetTmp[$budget->month][$budget->idStore]['tttxA'] = $budget->txA + $budget->txAl;
                $budgetTmp[$budget->month][$budget->idStore]['txE'] = $budget->txE;
            }
            
            $countBudgetTmp = 1;
            foreach($budgetTmp as $idxtmp => $tmp) {
                foreach($tmp as $idstore) {                    
                    if($countBudgetTmp == count($budgetTmp)) {
                        @$budgets['cattc'] += $idstore['totttc'];
                        @$budgets['caht'] += (($idstore['totttc']*0.995/1.07)+($idstore['totttc']*0.005/1.196));
                        @$budgets['nbt'] += ($idstore['totttc']/$idstore['tm']);
                        @$budgets['tttxA'] += $idstore['tttxA'];
                        @$budgets['txE'] += $idstore['txE'];
                    }
                }
                $countBudgetTmp++;
            }
            $dataSynthese->budgets = $budgets;
            //var_dump($budgets);
            
            return new ViewModel(array(
                'monthText' => $operationDate->tabMonthText(),

                ///////// PERIOD
                'periodText' => $operationDate->displayPeriodText($request),
                'period' => $operationDate->displayPeriod($request),

                ///////// STORE
                'store' => $whereQueryBySelection->getStoresNames($request),
                
            ));
        } else {
            return $this->redirect()->toRoute('operationtest/default');
        }
    }
    
    public function syntheseAction() {
        $dataSynthese = new Container('dataSynthese');
        
        // Get value for month and week
        $monthData = $weekData = array();
        foreach ($dataSynthese->offsetGet('period2') as $idxm => $month) {
            foreach ($month as $idxw => $week) {
                foreach ($week as $idxd => $day) {
                    // CA
                    if(!isset($weekData[$idxm][$idxw]['ca_ttc'])) $weekData[$idxm][$idxw]['ca_ttc'] = 0; 
                    if(!isset($weekData[$idxm][$idxw]['ca_ht'])) $weekData[$idxm][$idxw]['ca_ht'] = 0;
                    if(!isset($monthData[$idxm]['ca_ttc'])) $monthData[$idxm]['ca_ttc'] = 0;
                    if(!isset($monthData[$idxm]['ca_ht'])) $monthData[$idxm]['ca_ht'] = 0;
                        
                    foreach($dataSynthese->offsetGet('ca') as $ca_day) {
                        $orderDate = new \DateTime($ca_day['orderDate']);
                        // Week                        
                        if($orderDate->format('Y-m')==$idxm && $orderDate->format('W')==$idxw && $orderDate->format('Y-m-d')==$day->format('Y-m-d')){
                            $weekData[$idxm][$idxw]['ca_ttc'] += $ca_day['ca_ttc'];
                            $weekData[$idxm][$idxw]['ca_ht'] += $ca_day['ca_ht'];
                        }
                        // Month                        
                        if($orderDate->format(('Y-m'))==$idxm && $orderDate->format('Y-m-d')==$day->format('Y-m-d')) {
                            $monthData[$idxm]['ca_ttc'] += $ca_day['ca_ttc'];
                            $monthData[$idxm]['ca_ht'] += $ca_day['ca_ht'];
                        }
                    }
                    
                    // TRANSACTION
                    if(!isset($weekData[$idxm][$idxw]['nbT'])) $weekData[$idxm][$idxw]['nbT'] = 0; 
                    if(!isset($monthData[$idxm]['nbT'])) $monthData[$idxm]['nbT'] = 0;
                    
                    foreach($dataSynthese->offsetGet('nbT') as $nbt_day) {
                        $orderDate = new \DateTime($nbt_day['orderDate']);
                        // Week                        
                        if($orderDate->format('Y-m')==$idxm && $orderDate->format('W')==$idxw && $orderDate->format('Y-m-d')==$day->format('Y-m-d')){
                            $weekData[$idxm][$idxw]['nbT'] += $nbt_day['nbTransaction'];
                        }
                        // Month                        
                        if($orderDate->format(('Y-m'))==$idxm && $orderDate->format('Y-m-d')==$day->format('Y-m-d')) {
                            $monthData[$idxm]['nbT'] += $nbt_day['nbTransaction'];
                        }
                    }
                    
                    // ARTICLE
                    if(!isset($weekData[$idxm][$idxw]['nbA'])) $weekData[$idxm][$idxw]['nbA'] = 0; 
                    if(!isset($monthData[$idxm]['nbA'])) $monthData[$idxm]['nbA'] = 0;
                    
                    foreach($dataSynthese->offsetGet('nbA') as $nba_day) {
                        $orderDate = new \DateTime($nba_day['orderDate']);
                        // Week                        
                        if($orderDate->format('Y-m')==$idxm && $orderDate->format('W')==$idxw && $orderDate->format('Y-m-d')==$day->format('Y-m-d')){
                            $weekData[$idxm][$idxw]['nbA'] += $nba_day['nbArticle'];
                        }
                        // Month                        
                        if($orderDate->format(('Y-m'))==$idxm && $orderDate->format('Y-m-d')==$day->format('Y-m-d')) {
                            $monthData[$idxm]['nbA'] += $nba_day['nbArticle'];
                        }
                    }
                    
                    // ECART CAISSE
                    if(!isset($weekData[$idxm][$idxw]['ec'])) $weekData[$idxm][$idxw]['ec'] = 0; 
                    if(!isset($monthData[$idxm]['ec'])) $monthData[$idxm]['ec'] = 0;
                    
                    foreach($dataSynthese->offsetGet('ecDays') as $ec_day) {
                        $orderDate = new \DateTime($ec_day['date']);
                        // Week
                        if($orderDate->format('Y-m')==$idxm && $orderDate->format('W')==$idxw && $orderDate->format('Y-m-d')==$day->format('Y-m-d')){
                            $weekData[$idxm][$idxw]['ec'] += $ec_day['ec'];
                        }
                        // Month
                        if($orderDate->format(('Y-m'))==$idxm && $orderDate->format('Y-m-d')==$day->format('Y-m-d')) {
                            $monthData[$idxm]['ec'] += $ec_day['ec'];
                        }
                    }
                    
                    // ANNULATION CAISSE
                    if(!isset($weekData[$idxm][$idxw]['ac'])) $weekData[$idxm][$idxw]['ac'] = 0; 
                    if(!isset($weekData[$idxm][$idxw]['mac'])) $weekData[$idxm][$idxw]['mac'] = 0;
                    if(!isset($monthData[$idxm]['ac'])) $monthData[$idxm]['ac'] = 0;
                    if(!isset($monthData[$idxm]['mac'])) $monthData[$idxm]['mac'] = 0;
                    
                    foreach($dataSynthese->offsetGet('ac') as $ac_day) {
                        $orderDate = new \DateTime($ac_day['orderDate']);
                        // Week                        
                        if($orderDate->format('Y-m')==$idxm && $orderDate->format('W')==$idxw && $orderDate->format('Y-m-d')==$day->format('Y-m-d')){
                            $weekData[$idxm][$idxw]['ac'] += $ac_day['ac'];
                            $weekData[$idxm][$idxw]['mac'] += $ac_day['montant_ac'];
                        }
                        // Month                        
                        if($orderDate->format(('Y-m'))==$idxm && $orderDate->format('Y-m-d')==$day->format('Y-m-d')) {
                            $monthData[$idxm]['ac'] += $ac_day['ac'];
                            $monthData[$idxm]['mac'] += $ac_day['montant_ac'];
                        }
                    }
                    
                    // VENTILATION MODE DE PAIEMENT
                    if(!isset($weekData[$idxm][$idxw]['cb'])) $weekData[$idxm][$idxw]['cb'] = 0;
                    if(!isset($weekData[$idxm][$idxw]['espece'])) $weekData[$idxm][$idxw]['espece'] = 0;
                    if(!isset($weekData[$idxm][$idxw]['cheque'])) $weekData[$idxm][$idxw]['cheque'] = 0;
                    if(!isset($weekData[$idxm][$idxw]['tr'])) $weekData[$idxm][$idxw]['tr'] = 0;
                    
                    if(!isset($monthData[$idxm]['cb'])) $monthData[$idxm]['cb'] = 0;
                    if(!isset($monthData[$idxm]['espece'])) $monthData[$idxm]['espece'] = 0;
                    if(!isset($monthData[$idxm]['cheque'])) $monthData[$idxm]['cheque'] = 0;
                    if(!isset($monthData[$idxm]['tr'])) $monthData[$idxm]['tr'] = 0;
                    foreach($dataSynthese->offsetGet('banque') as $bq_day) {
                        $orderDate = new \DateTime($bq_day['date']);
                        // Week
                        if($orderDate->format('Y-m')==$idxm && $orderDate->format('W')==$idxw && $orderDate->format('Y-m-d')==$day->format('Y-m-d')) {
                            $weekData[$idxm][$idxw]['cb'] += $bq_day['cb'];
                            $weekData[$idxm][$idxw]['espece'] += $bq_day['espece'];
                            $weekData[$idxm][$idxw]['cheque'] += $bq_day['cheque'];
                            $weekData[$idxm][$idxw]['tr'] += $bq_day['tr'];
                        }
                        // Month
                        if($orderDate->format('Y-m')==$idxm && $orderDate->format('Y-m-d')==$day->format('Y-m-d')) {
                            $monthData[$idxm]['cb'] += $bq_day['cb'];
                            $monthData[$idxm]['espece'] += $bq_day['espece'];
                            $monthData[$idxm]['cheque'] += $bq_day['cheque'];
                            $monthData[$idxm]['tr'] += $bq_day['tr'];
                        }
                    }
                    
                    // REPAS
                    if(!isset($weekData[$idxm][$idxw]['repas'])) $weekData[$idxm][$idxw]['repas'] = 0; 
                    if(!isset($monthData[$idxm]['repas'])) $monthData[$idxm]['repas'] = 0;
                    
                    foreach($dataSynthese->offsetGet('repasDays') as $repas_day) {
                        $orderDate = new \DateTime($repas_day['orderDate']);
                        // Week                        
                        if($orderDate->format('Y-m')==$idxm && $orderDate->format('W')==$idxw && $orderDate->format('Y-m-d')==$day->format('Y-m-d')){
                            $weekData[$idxm][$idxw]['repas'] += $repas_day['repas'];
                        }
                        // Month                        
                        if($orderDate->format(('Y-m'))==$idxm && $orderDate->format('Y-m-d')==$day->format('Y-m-d')) {
                            $monthData[$idxm]['repas'] += $repas_day['repas'];
                        }
                    }
                    
                    // Ventilation TVA
                    if(!isset($weekData[$idxm][$idxw]['txreduit1'])) $weekData[$idxm][$idxw]['txreduit1'] = 0;
                    if(!isset($weekData[$idxm][$idxw]['txreduit2'])) $weekData[$idxm][$idxw]['txreduit2'] = 0;
                    if(!isset($weekData[$idxm][$idxw]['txnormal'])) $weekData[$idxm][$idxw]['txnormal'] = 0;
                    
                    if(!isset($monthData[$idxm]['txreduit1'])) $monthData[$idxm]['txreduit1'] = 0;
                    if(!isset($monthData[$idxm]['txreduit2'])) $monthData[$idxm]['txreduit2'] = 0;
                    if(!isset($monthData[$idxm]['txnormal'])) $monthData[$idxm]['txnormal'] = 0;
                    
                    foreach($dataSynthese->offsetGet('vtva') as $vtva_day) {
                        $orderDate = new \DateTime($vtva_day['orderDate']);
                        // Week
                        if($orderDate->format('Y-m')==$idxm && $orderDate->format('W')==$idxw && $orderDate->format('Y-m-d')==$day->format('Y-m-d')) {
                            if($vtva_day['taxeRate']==5.5){
                                $weekData[$idxm][$idxw]['txreduit1'] += $vtva_day['ca_ttc'];
                            }
                            if($vtva_day['taxeRate']==7 || $vtva_day['taxeRate']==10){
                                $weekData[$idxm][$idxw]['txreduit2'] += $vtva_day['ca_ttc'];
                            }
                            if($vtva_day['taxeRate']==19.6 || $vtva_day['taxeRate']==20){
                                $weekData[$idxm][$idxw]['txnormal'] += $vtva_day['ca_ttc'];
                            }
                        }
                        // Month
                        if($orderDate->format('Y-m')==$idxm && $orderDate->format('Y-m-d')==$day->format('Y-m-d')) {
                            if($vtva_day['taxeRate']==5.5){
                                $monthData[$idxm]['txreduit1'] += $vtva_day['ca_ttc'];
                            }
                            if($vtva_day['taxeRate']==7 || $vtva_day['taxeRate']==10){
                                $monthData[$idxm]['txreduit2'] += $vtva_day['ca_ttc'];
                            }
                            if($vtva_day['taxeRate']==19.6 || $vtva_day['taxeRate']==20){
                                $monthData[$idxm]['txnormal'] += $vtva_day['ca_ttc'];
                            }
                        }
                    }
                }
            }
        }
//        var_dump($weekData);
//        var_dump($monthData);
        
        $variables = array(
            'ca' => $dataSynthese->offsetGet('ca'),
            'vtva' => $dataSynthese->offsetGet('vtva'),
            'tm' => $dataSynthese->offsetGet('tm'),
            'nbT' => $dataSynthese->offsetGet('nbT'),
            'nbA' => $dataSynthese->offsetGet('nbA'),
            'period' => $dataSynthese->offsetGet('period'),
            'echoMonthtoEn' => $this->echoMonthtoEn(),
            'meteoDays' => $dataSynthese->offsetGet('meteoDays'),
            'ecDays' => $dataSynthese->offsetGet('ecDays'), 
            'repasDays' => $dataSynthese->offsetGet('repasDays'),
            'ac' => $dataSynthese->offsetGet('ac'),
            'banque' => $dataSynthese->offsetGet('banque'),
            'mo' => $dataSynthese->offsetGet('mo'),
            
            'monthData' => $monthData,
            'weekData' => $weekData,
            'budgets' => $dataSynthese->offsetGet('budgets'),
        );
        
        $viewModel = new ViewModel();
        $viewModel->setVariables($variables);
        $viewModel->setTerminal(true);
        
        return $viewModel;
    }
    
    public function makegraphiqueAction() {
        $dataSynthese = new Container('dataSynthese');
        $variables = array(
            'id' => $this->params('id'),
            'ca' => $dataSynthese->offsetGet('ca'),
            'vtva' => $dataSynthese->offsetGet('vtva'),
            'tm' => $dataSynthese->offsetGet('tm'),
            'nbT' => $dataSynthese->offsetGet('nbT'),
            'nbA' => $dataSynthese->offsetGet('nbA'),
            'meteoDays' => $dataSynthese->offsetGet('meteoDays'),
            'ecDays' => $dataSynthese->offsetGet('ecDays'), 
            'repasDays' => $dataSynthese->offsetGet('repasDays'),
            'ac' => $dataSynthese->offsetGet('ac'),
            'banque' => $dataSynthese->offsetGet('banque'),
            'mo' => $dataSynthese->offsetGet('mo'),
            'budgets' => $dataSynthese->offsetGet('budgets'),
        );
        
        $viewModel = new ViewModel();
        $viewModel->setVariables($variables);
        $viewModel->setTerminal(true);
        
        return $viewModel;
    }
    
    public function graphiqueAction() {        
        $viewModel = new ViewModel();
        $viewModel->setTerminal(true);
        
        return $viewModel;
    }
    
    public function detailsAction() {
        $dataSynthese = new Container('dataSynthese');
        $variables = array(
            'ca' => $dataSynthese->offsetGet('ca2'),
            'tm' => $dataSynthese->offsetGet('tm'),
            'nbT' => $dataSynthese->offsetGet('nbT2'),
            'nbA' => $dataSynthese->offsetGet('nbA2'),
            'period' => $dataSynthese->offsetGet('period'),
            'store' => $dataSynthese->offsetGet('store'),
            'monthText' => $this->echoMonthText(),
            'echoMonthtoEn' => $this->echoMonthtoEn(),
            'ec' => $dataSynthese->offsetGet('ecDays2'), 
            'repas' => $dataSynthese->offsetGet('repasDays2'),
            'ac' => $dataSynthese->offsetGet('ac2'),
            'banque' => $dataSynthese->offsetGet('banque2'),
            'mo' => $dataSynthese->offsetGet('mo2'),
        );
        
        $viewModel = new ViewModel();
        $viewModel->setVariables($variables);
        $viewModel->setTerminal(true);
        
        return $viewModel;
    }
    
    public function accessrapportAction() {        
        $viewModel = new ViewModel();
        $viewModel->setTerminal(true);
        return $viewModel;
    }
    
    public function rapportproduitAction() {      
        $table = new Table($sm = $this->getServiceLocator());
        $dataSynthese = new Container('dataSynthese');
        $variables = array(
            'ca' => $dataSynthese->offsetGet('ca'),
            'tm' => $dataSynthese->offsetGet('tm'),
            'nbT' => $dataSynthese->offsetGet('nbT'),
            'nbA' => $dataSynthese->offsetGet('nbA'),
            'period' => $dataSynthese->offsetGet('period'),
            'rptProduit' => $dataSynthese->offsetGet('rptProduit'),
            'families' => $table->getSalerowTable()->getFamilies(), //$this->getSalerowTable()->getFamilies(),
            'store' => $dataSynthese->offsetGet('store'),
            'monthText' => $this->echoMonthText(),
        );        
        $viewModel = new ViewModel();
        $viewModel->setVariables($variables);
        $viewModel->setTerminal(true);        
        return $viewModel;
    }
    
    public function rapportproduit2Action() {      
        $table = new Table($sm = $this->getServiceLocator());
        $dataSynthese = new Container('dataSynthese');
        $variables = array(
            'ca' => $dataSynthese->offsetGet('ca'),
            'tm' => $dataSynthese->offsetGet('tm'),
            'nbT' => $dataSynthese->offsetGet('nbT'),
            'nbA' => $dataSynthese->offsetGet('nbA'),
            'period' => $dataSynthese->offsetGet('period'),
            'rptProduit' => $dataSynthese->offsetGet('rptProduit'),
            'families' => $table->getSalerowTable()->getFamilies(), //$this->getSalerowTable()->getFamilies(),
            'store' => $dataSynthese->offsetGet('store'),
            'monthText' => $this->echoMonthText(),
        );        
        $viewModel = new ViewModel();
        $viewModel->setVariables($variables);
        $viewModel->setTerminal(true);        
        return $viewModel;
    }
    
    public function rapportproduit3Action() {      
        $table = new Table($sm = $this->getServiceLocator());
        $dataSynthese = new Container('dataSynthese');
        $variables = array(
            'ca' => $dataSynthese->offsetGet('ca'),
            'tm' => $dataSynthese->offsetGet('tm'),
            'nbT' => $dataSynthese->offsetGet('nbT'),
            'nbA' => $dataSynthese->offsetGet('nbA'),
            'period' => $dataSynthese->offsetGet('period'),
            'rptProduit' => $dataSynthese->offsetGet('rptProduit'),
            'families' => $table->getSalerowTable()->getFamilies(), //$this->getSalerowTable()->getFamilies(),
            'store' => $dataSynthese->offsetGet('store'),
            'monthText' => $this->echoMonthText(),
        );        
        $viewModel = new ViewModel();
        $viewModel->setVariables($variables);
        $viewModel->setTerminal(true);        
        return $viewModel;
    }
    
    function getQuery($request) {
        $storeIds = array();
        foreach($request->getPost()->selectable as $storeId){
            $tabStoreId = explode("-", $storeId);
            if(count($tabStoreId) == 1) { // Récupères les boutiques choisies
                $storeIds[] = $storeId;
            } else { // Récupère les boutiques du marché choisi
                $mStores = $this->getMarchestoreTable()->getMarcheStore($tabStoreId[1]);
                if($tabStoreId[1] == 1) { // Si le marché Cojean est choisi
                    $stores = $this->getStoreTable()->fetchAll();
                    foreach ($stores as $store) {
                        $storeIds[] = $store->idStore;
                    }
                } else { // Si autre marché que Cojean est choisi
                    foreach($mStores as $mstore) {
                        $storeIds[] = $mstore['idStore'];
                    }
                }
            }
        }
        $storeWhere = "idStore IN (".implode(',', $storeIds).")";
        $whereS = $storeWhere;
        $whereOther = $storeWhere;
        $whereS2 = "";
        $whereOther2 = "";
        $query = array();

        if($request->getPost()->periodY){
            $counterA = 0;
            if($request->getPost()->periodM){
                if($request->getPost()->semaine){ ////////////// Année + Mois + Semaine
                    foreach($request->getPost()->periodY as $year):
                        foreach($request->getPost()->periodM as $month):
                            foreach($request->getPost()->semaine as $day):
                                if($whereS2 == "") {
                                    $whereS2 .= " AND ( ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                    $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month;
                                    $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                } else {
                                    $whereS2 .= " OR ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                    $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month;
                                    $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                }
                                
                                if($whereOther2 == "") {
                                    $whereOther2 .= " AND ( ( EXTRACT(YEAR FROM date) = ".$year;
                                    $whereOther2 .= " AND EXTRACT(MONTH FROM date) = ".$month;
                                    $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                } else {
                                    $whereOther2 .= " OR ( EXTRACT(YEAR FROM date) = ".$year;
                                    $whereOther2 .= " AND EXTRACT(MONTH FROM date) = ".$month;
                                    $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                }
                            endforeach;
                        endforeach;
                        $counterA++;
                    endforeach;
                } else { ////////////// Année + Mois
                    foreach($request->getPost()->periodY as $year):
                        foreach($request->getPost()->periodM as $month):
                            if($whereS2 == "") {
                                $whereS2 .= " AND ( ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month.")";
                            } else {
                                $whereS2 .= " OR ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month.")";
                            }
                            
                            if($whereOther2 == "") {
                                $whereOther2 .= " AND ( ( EXTRACT(YEAR FROM date) = ".$year;
                                $whereOther2 .= " AND EXTRACT(MONTH FROM date) = ".$month.")";
                            } else {
                                $whereOther2 .= " OR ( EXTRACT(YEAR FROM date) = ".$year;
                                $whereOther2 .= " AND EXTRACT(MONTH FROM date) = ".$month.")";
                            }
                        endforeach;

                        $counterA++;
                    endforeach;
                }

                if($counterA == count($request->getPost()->periodY)){
                    $whereS2 .= " )";
                    $whereOther2 .= " )";
                }
            } else {
                if($request->getPost()->semaine){ ////////////// Année + Semaine 
                    foreach($request->getPost()->periodY as $year):
                        foreach($request->getPost()->semaine as $day):
                            if($whereS2 == "") {
                                $whereS2 .= " AND ( ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                            } else {
                                $whereS2 .= " OR ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                            }
                            
                            if($whereOther2 == "") {
                                $whereOther2 .= " AND ( ( EXTRACT(YEAR FROM date) = ".$year;
                                $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                            } else {
                                $whereOther2 .= " OR ( EXTRACT(YEAR FROM date) = ".$year;
                                $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                            }
                        endforeach;

                        $counterA++;
                    endforeach;
                } else { ////////////// Année
                    foreach($request->getPost()->periodY as $year):
                        if($whereS2 == "") {
                            $whereS2 .= " AND ( EXTRACT(YEAR FROM orderDate) = ".$year;
                        } else {
                            $whereS2 .= " OR EXTRACT(YEAR FROM orderDate) = ".$year;
                        }
                        
                        if($whereOther2 == "") {
                            $whereOther2 .= " AND ( EXTRACT(YEAR FROM date) = ".$year;
                        } else {
                            $whereOther2 .= " OR EXTRACT(YEAR FROM date) = ".$year;
                        }
                        $counterA++;
                    endforeach;
                }
                if($counterA == count($request->getPost()->periodY)){
                    $whereS2 .= " )";
                    $whereOther2 .= " )";
                }
            } 
        } else {
            if($request->getPost()->periodM){
                $counterM = 0;
                if($request->getPost()->semaine){ ////////////// Mois + Semaine
                    foreach($request->getPost()->periodM as $month):
                        foreach($request->getPost()->semaine as $day):
                            if($whereS2 == "") {
                                $whereS2 .= " AND ( ( EXTRACT(MONTH FROM orderDate) = ".$month;
                                $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                            } else {
                                $whereS2 .= " OR ( EXTRACT(MONTH FROM orderDate) = ".$month;
                                $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                            }
                            
                            if($whereOther2 == "") {
                                $whereOther2 .= " AND ( ( EXTRACT(MONTH FROM date) = ".$month;
                                $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                            } else {
                                $whereOther2 .= " OR ( EXTRACT(MONTH FROM date) = ".$month;
                                $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                            }
                        endforeach;

                        $counterM++;
                    endforeach;
                } else { ////////////// Mois
                    foreach($request->getPost()->periodM as $month):
                        if($whereS2 == "") {
                            $whereS2 .= " AND ( EXTRACT(MONTH FROM orderDate) = ".$month;
                        } else {
                            $whereS2 .= " OR EXTRACT(MONTH FROM orderDate) = ".$month;
                        }
                        
                        if($whereOther2 == "") {
                            $whereOther2 .= " AND ( EXTRACT(MONTH FROM date) = ".$month;
                        } else {
                            $whereOther2 .= " OR EXTRACT(MONTH FROM date) = ".$month;
                        }

                        $counterM++;
                    endforeach;
                }
                if($counterM == count($request->getPost()->periodM)){
                    $whereS2 .= " )";
                    $whereOther2 .= " )";
                }
            } else {
                if($request->getPost()->semaine){  ////////////// Semaine
                    $counterS = 0;
                    foreach($request->getPost()->semaine as $day):
                        if($whereS2 == "") {
                            $whereS2 .= " AND ( DAYOFWEEK(orderDate) = ".($day+1); // (DAYOFWEEK, Dimanche=1 et non 0)
                        } else {
                            $whereS2 .= " OR DAYOFWEEK(orderDate) = ".($day+1); // (DAYOFWEEK, Dimanche=1 et non 0)
                        }
                        
                        if($whereOther2 == "") {
                            $whereOther2 .= " AND ( DAYOFWEEK(date) = ".($day+1); // (DAYOFWEEK, Dimanche=1 et non 0)
                        } else {
                            $whereOther2 .= " OR DAYOFWEEK(date) = ".($day+1); // (DAYOFWEEK, Dimanche=1 et non 0)
                        }

                        $counterS++;
                    endforeach;

                    if($counterS == count($request->getPost()->semaine)) {
                        $whereS2 .= " )";
                        $whereOther2 .= " )";
                    }
                }
            }
        }

        
        $whereS .= $whereS2;        
        $whereOther .= $whereOther2;  
        

        ///////////////////// PERIODE TEXT ///////////////////////
        $period = "";
        $periodM = "";
        $periodY = "";
        $periodJ = "";
        $monthText = $this->echoMonthText();
        $dayText = $this->echoShortDayText();

        if($request->getPost()->periodM){
            $i = 1;
            foreach($request->getPost()->periodM as $mois):
                if($i < count($request->getPost()->periodM))
                    $periodM .= $monthText[$mois].", ";
                else
                    $periodM .= $monthText[$mois];
                $i++;
            endforeach;
        }
        if($request->getPost()->periodY){
            $i = 1;
            $periodY .= ' ';
            foreach($request->getPost()->periodY as $annee):                    
                if($i < count($request->getPost()->periodY))
                    $periodY .= $annee.", ";
                else
                    $periodY .= $annee;
                $i++;
            endforeach;
        }
        if($request->getPost()->semaine){
            $i = 1;
            $periodJ .= ' - ';
            foreach($request->getPost()->semaine as $jour):                    
                if($i < count($request->getPost()->semaine))
                    $periodJ .= $dayText[$jour]." ";
                else
                    $periodJ .= $dayText[$jour];
                $i++;
            endforeach;
        }

        $period = $periodM.$periodY.$periodJ;
        $period = ($period != "")?$period:"Toute la période";

        $query['where'] = $whereS;
        $query['whereOther'] = $whereOther;
        $query['period'] = $period;

        return $query;        
    }
    
    public function echoShortDayText()
    {
        $dayText[0] = 'dim';
        $dayText[1] = 'lun';
        $dayText[2] = 'mar';
        $dayText[3] = 'mer';
        $dayText[4] = 'jeu';
        $dayText[5] = 'ven';
        $dayText[6] = 'sam';        
        
        return $dayText;
    }
    
    public function echoLongDayText()
    {
        $dayText[0] = 'dimanche';
        $dayText[1] = 'lundi';
        $dayText[2] = 'mardi';
        $dayText[3] = 'mercredi';
        $dayText[4] = 'jeudi';
        $dayText[5] = 'vendredi';
        $dayText[6] = 'samedi';        
        
        return $dayText;
    }
    
    public function echoMonthText()
    {
        $monthText[1] = 'Janvier';
        $monthText[2] = 'Fevrier';
        $monthText[3] = 'Mars';
        $monthText[4] = 'Avril';
        $monthText[5] = 'Mai';
        $monthText[6] = 'Juin';
        $monthText[7] = 'Juillet';
        $monthText[8] = 'Août';
        $monthText[9] = 'Septembre';
        $monthText[10] = 'Octobre';
        $monthText[11] = 'Novembre';
        $monthText[12] = 'Décembre';
        
        return $monthText;
    }
    
    public function echoMonthtoEn()
    {
        $monthText['Janvier'] = 'January';
        $monthText['Fevrier'] = 'February';
        $monthText['Mars'] = 'March';
        $monthText['Avril'] = 'April';
        $monthText['Mai'] = 'May';
        $monthText['Juin'] = 'June';
        $monthText['Juillet'] = 'July';
        $monthText['Août'] = 'August';
        $monthText['Septembre'] = 'September';
        $monthText['Octobre'] = 'October';
        $monthText['Novembre'] = 'November';
        $monthText['Décembre'] = 'December';
        
        return $monthText;
    }
    
    
}
?>
